﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using SlamCms.Common;

namespace SlamCms.Data.Repositories
{
	public class TrackingRepository : ITrackingRepository
	{
		private readonly IDbConnection connection;

		public TrackingRepository(IDbConnection connection)
		{
			this.connection = connection;
		}

		public IEnumerable<TrackNotFound> GetNotFound(string searchText)
		{
			var sql = @"select * from [dbo].[TrackNotFound] with (nolock)";
			if (!searchText.IsNullOrEmpty())
				sql += @" where [Url] like '%' + @searchText + '%'";
			sql += @" order by [Date] desc, [Count] desc";

			return connection.Query<TrackNotFound>(sql, new { searchText });
		}

		public void ClearNotFound()
		{
			connection.Execute(@"delete from [dbo].[TrackNotFound]", new {});
		}

		public void Save(IEnumerable<TrackPage> trackPages)
		{
			connection.Execute(@"if not exists (select 1 from TrackVisit with (nolock) where TrackVisitId = @TrackVisitId)
									insert into TrackVisit (TrackVisitId, UserId, CreatedDate, Referrer) values (@TrackVisitId, @UserId, @Date, @Referrer)
								 else
									update TrackVisit set UserId = @UserId where TrackVisitId = @TrackVisitId
		
								 insert into TrackPage (TrackPageId, TrackVisitId, What, Date, How, Referrer, ContentItemId) 
									values (@TrackPageId, @TrackVisitId, @What, @Date, @How, @Referrer, @ContentItemId)", trackPages);
		}

		public void Save(IEnumerable<TrackNotFound> trackNotFounds)
		{
			connection.Execute(@"if exists (select 1 from TrackNotFound with (nolock) where Url = @Url and Referrer = @Referrer) 
									update TrackNotFound set Count = Count + 1, Date = @Date where Url = @Url and Referrer = @Referrer
								 else
									insert into TrackNotFound (Url, Date, Referrer, UserId, Count) 
									values (@Url, @Date, @Referrer, @UserId, @Count)", trackNotFounds);
		}

		public void Save(IEnumerable<TrackTagHit> trackTagHits)
		{
			var objectsToInsert = trackTagHits.Select(x => new
			{
				x.TrackTagHitId,
				x.TrackVisitId,
				x.TagIdentifier,
				x.Date,
				x.Referrer,
				x.UserId
			});

			// just one taghit per session
			connection.Execute(@"declare @TagId varchar(25)
								 set @TagId = null
								 select @TagId = TagID from Tag with (nolock) where Identifier = @TagIdentifier
								 
								 if (@TagId is not null) begin
									 if not exists (select 1 from TrackVisit with (nolock) where TrackVisitId = @TrackVisitId)
										insert into TrackVisit (TrackVisitId, UserId, CreatedDate, Referrer) values (@TrackVisitId, @UserId, @Date, @Referrer)
									 else
										update TrackVisit set UserId = @UserId where TrackVisitId = @TrackVisitId
								
									 if not exists (select 1 from TrackTagHit with (nolock) where TrackVisitId = @TrackVisitId and TagId = @TagId) begin 
										 insert into TrackTagHit (TrackTagHitId, TrackVisitId, TagId, Date) 
										 values (@TrackTagHitId, @TrackVisitId, @TagId, @Date)
									 end
								 end", objectsToInsert);
		}

//        public IEnumerable<ContentItemStatics> GetContentItemClicks(Guid siteId, DateTime fromDate, DateTime toDate)
//        {
//            string query = @"  SELECT distinct T.[ContentItemId] as ContentItemId, COUNT( T.[TrackPageID] ) As Clicks 
//                                From [KnowledgeCenterAndInSite].[dbo].[TrackPage] T
//                                join [KnowledgeCenterAndInSite].[dbo].[ContentItem] CI on T.ContentItemId = CI.ContentItemID
//                                join [KnowledgeCenterAndInSite].[SLAM].[List] L on CI.ListID = L.ListID
//                                where L.[SiteID] = @siteID
//                                AND T.[Date] >= @fromDate and T.[Date] < @toDate 
//                                Group By T.[ContentItemId]";

//            var statics = connection.Query<ContentItemStatics>(query, new { siteID = siteId, fromDate = fromDate.Date, toDate = toDate.AddDays(1).Date});

//            return statics;
//        }


		public IEnumerable<ContentItemStatics> GetSiteContentItemClicks(Guid siteId, DateTime fromDate, DateTime toDate)
		{
			string query = @"  select distinct TV.UserID as UserId, COUNT(TP.[TrackPageID])as clicks
							   from [TrackPage] TP 
							   inner join [TrackVisit] TV on TV.[TrackVisitID] = TP.[TrackVisitID]
							   right join [ContentItem] CI on TP.ContentItemId = CI.ContentItemID
							   right join [SLAM].[List] L on CI.ListID = L.ListID    
							   where L.[SiteID] =  @siteID
							   and TP.[Date] >= @fromDate and TP.[Date] < @toDate
							   and TV.UserID is not null
							   group by TV.UserID";

			var statics = connection.Query<ContentItemStatics>(query, new { siteID = siteId, fromDate = fromDate.Date, toDate = toDate.AddDays(1).Date });

			return statics;
		}

		public IEnumerable<ContentItemStatics> GetContentItemClicksByZone(string zoneId, DateTime fromDate, DateTime toDate)
		{
			string query = @"  select distinct TV.UserID as UserId, COUNT(TP.[TrackPageID])as clicks
							   from [TrackPage] TP 
							   inner join [TrackVisit] TV on TV.[TrackVisitID] = TP.[TrackVisitID]
							   right join [ContentItem] CI on TP.ContentItemId = CI.ContentItemID
							   right join [SLAM].[List] L on CI.ListID = L.ListID
							   right join [SLAM].[Site] S on L.SiteID = S.SiteID
							   right join [ZoneSite] Z on Z.SiteID = S.SiteID
							   where TP.[Date] >= @fromDate and TP.[Date]<@toDate 
							   and TV.UserID is not null
							   and Z.ZoneID = @zoneId
							   group by TV.UserID";

		   


			var statics = connection.Query<ContentItemStatics>(query, new { zoneId = zoneId, fromDate = fromDate.Date, toDate = toDate.AddDays(1).Date });

			return statics;
		}

		public IEnumerable<int> GetLoggedInUsersByZone(string zoneId, DateTime fromDate, DateTime toDate)
		{
			string query = @"   select distinct TV.UserID as UserID
								from [TrackPage] TP 
								inner join [TrackVisit] TV on TV.[TrackVisitID] = TP.[TrackVisitID]
								right join [ContentItem] CI on TP.ContentItemId = CI.ContentItemID
								right join [SLAM].[List] L on CI.ListID = L.ListID
								right join [SLAM].[Site] S on L.SiteID = S.SiteID
								right join [ZoneSite] Z on Z.SiteID = S.SiteID
								where TP.[Date] >= @fromDate and TP.[Date]<@toDate 
								and Z.ZoneID = @zoneId
								and TV.UserID is not null
								order by TV.UserID";

			var statics = connection.Query<int>(query, new { zoneId = zoneId, fromDate = fromDate.Date, toDate = toDate.AddDays(1).Date });

			return statics;
		}


		public IEnumerable<UsersAls> GetUsersALSByZone(string zoneId, DateTime fromDate, DateTime toDate)
		{
			string query = @"   select UserId, AVG(mins) ALS
									from (select TV.UserID UserID, TP.TrackVisitID, DATEDIFF(n, min(date), max(date)) mins
											from TrackPage TP
											inner join [TrackVisit] TV on TV.[TrackVisitID] = TP.TrackVisitID
											right join [ContentItem] CI on TP.ContentItemId = CI.ContentItemID
											right join [SLAM].[List] L on CI.ListID = L.ListID
											right join [SLAM].[Site] S on L.SiteID = S.SiteID
											right join [ZoneSite] Z on Z.SiteID = S.SiteID
											where TP.[Date] >= @fromDate and TP.[Date]<@toDate 
											and TV.UserID is not null
											and Z.ZoneID = @zoneId
											group by TP.TrackVisitID, TV.UserID) as temp
											group by UserId";

			var statics = connection.Query<UsersAls>(query, new { zoneId = zoneId, fromDate = fromDate.Date, toDate = toDate.AddDays(1).Date });

			return statics;
		}


	}
}
